﻿using DBUtil;
using Function;
using Model.Permission;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL.Permission.Agent
{
    public class UserDAL
    {
        DbHelperSQLP DbHelperSQL = new DbHelperSQLP(PubConstant.JHAgent);
        public UserDAL()
        { }

        /// <summary>
        /// 更新某用户的密码
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool UpdatePwd(int uid, string pwd)
        {
            var sql = "UPDATE Permission.[User] SET [Password] = @U_PassWord WHERE UserID=@P_Id";
            SqlParameter[] parameters = {
					new SqlParameter("@P_Id", uid),
                    new SqlParameter("@U_PassWord", pwd)
			};
            return (DbHelperSQL.ExecuteSql(sql, parameters) > 0);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public bool DisableUser(int uid)
        {
            var sql = "UPDATE Permission.[User] SET [Status] = 3 WHERE UserID=@pid";
            SqlParameter[] parameters = {
					new SqlParameter("@pid", SqlDbType.Int,4)
			};
            parameters[0].Value = uid;
            return (DbHelperSQL.ExecuteSql(sql, parameters) > 0);
        }

        /// <summary>
        /// 是否存在该记录
        /// <param name="LoginName">登录名</param>
        /// <param name="Password">密码</param>
        /// </summary>
        public int? Exists(string LoginName, string Password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from Permission.[User]");
            strSql.Append(" where [LoginName]=@LoginName and [Password]=@Password AND Status=1");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName),
                    new SqlParameter("@Password", Password)
                                        };
            var reader = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            if (reader.Read())
                return reader.GetInt32(0);
            return null;
        }

        /// <summary>
        /// 用户名是否存在
        /// </summary>
        /// <param name="LoginName">登录名</param>
        /// <returns></returns>
        public bool ExistsName(string LoginName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from Permission.[User]");
            strSql.Append(" where [LoginName]=@LoginName");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName)
                                        };
            var reader = DbHelperSQL.Exists(strSql.ToString(), parameters);
            return reader;
        }

        /// <summary>
        /// 是否存在当前用户名以外的其他用户名
        /// </summary>
        /// <param name="LoginName">登录名</param>
        /// <param name="UserID">密码</param>
        /// <returns></returns>
        public bool ExistsOtherUser(string LoginName, int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from Permission.[User]");
            strSql.Append(" where [LoginName]=@LoginName and UserID<>@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName),
                    new SqlParameter("@UserID", UserID),
                                        };
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Permission.[User]");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(UserEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Permission.[User](");
            strSql.Append("GroupID,LoginName,Password,CreateTime,Status,TrueName,MobilePhone,Phone,Address,LastLoginTime,LastLoginIP,IsOnline,IsLimit,Note)");
            strSql.Append(" values (");
            strSql.Append("@GroupID,@LoginName,@Password,@CreateTime,@Status,@TrueName,@MobilePhone,@Phone,@Address,@LastLoginTime,@LastLoginIP,@IsOnline,@IsLimit,@Note)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@LoginName", SqlDbType.NVarChar,30),
					new SqlParameter("@Password", SqlDbType.NVarChar,32),
					new SqlParameter("@CreateTime", SqlDbType.NVarChar,50),
					new SqlParameter("@Status", SqlDbType.TinyInt,1),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,30),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar,20),
					new SqlParameter("@Phone", SqlDbType.VarChar,20),
					new SqlParameter("@Address", SqlDbType.NVarChar,200),
					new SqlParameter("@LastLoginTime", SqlDbType.NVarChar,50),
					new SqlParameter("@LastLoginIP", SqlDbType.VarChar,15),
					new SqlParameter("@IsOnline", SqlDbType.TinyInt,1),
					new SqlParameter("@IsLimit", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NVarChar,100)};
            parameters[0].Value = model.GroupID;
            parameters[1].Value = model.LoginName;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.CreateTime;
            parameters[4].Value = model.Status;
            parameters[5].Value = model.TrueName;
            parameters[6].Value = model.MobilePhone;
            parameters[7].Value = model.Phone;
            parameters[8].Value = model.Address;
            parameters[9].Value = model.LastLoginTime;
            parameters[10].Value = model.LastLoginIP;
            parameters[11].Value = model.IsOnline;
            parameters[12].Value = model.IsLimit;
            parameters[13].Value = model.Note;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据 不更新密码
        /// </summary>
        public bool Update(UserEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Permission.[User] set ");
            strSql.Append("GroupID=@GroupID,");
            strSql.Append("LoginName=@LoginName,");
            strSql.Append("Status=@Status,");
            strSql.Append("TrueName=@TrueName,");
            strSql.Append("MobilePhone=@MobilePhone,");
            strSql.Append("Phone=@Phone,");
            strSql.Append("Address=@Address,");
            strSql.Append("LastLoginTime=@LastLoginTime,");
            strSql.Append("LastLoginIP=@LastLoginIP,");
            strSql.Append("IsOnline=@IsOnline,");
            strSql.Append("IsLimit=@IsLimit,");
            strSql.Append("Note=@Note");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@LoginName", SqlDbType.NVarChar,30),
					new SqlParameter("@Status", SqlDbType.TinyInt,1),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,30),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar,20),
					new SqlParameter("@Phone", SqlDbType.VarChar,20),
					new SqlParameter("@Address", SqlDbType.NVarChar,200),
					new SqlParameter("@LastLoginTime", SqlDbType.NVarChar,50),
					new SqlParameter("@LastLoginIP", SqlDbType.VarChar,15),
					new SqlParameter("@IsOnline", SqlDbType.TinyInt,1),
					new SqlParameter("@IsLimit", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NVarChar,100),
					new SqlParameter("@UserID", SqlDbType.Int,4)};
            parameters[0].Value = model.GroupID;
            parameters[1].Value = model.LoginName;
            parameters[2].Value = model.Status;
            parameters[3].Value = model.TrueName;
            parameters[4].Value = model.MobilePhone;
            parameters[5].Value = model.Phone;
            parameters[6].Value = model.Address;
            parameters[7].Value = model.LastLoginTime;
            parameters[8].Value = model.LastLoginIP;
            parameters[9].Value = model.IsOnline;
            parameters[10].Value = model.IsLimit;
            parameters[11].Value = model.Note;
            parameters[12].Value = model.UserID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int UserID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Permission.[User] ");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string UserIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Permission.[User] ");
            strSql.Append(" where UserID in (" + UserIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// <param name="AgentID">代理商id</param>
        /// </summary>
        public AgentUser GetModelByAgent(int AgentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserID,AgentID,GroupID,LoginName,Password,CreateTime,Status,TrueName,MobilePhone,Phone,Address,LastLoginTime,LastLoginIP,IsOnline,IsLimit,Note from permission.[User] ");
            strSql.Append(" where AgentID=@AgentID");
            SqlParameter[] parameters = {
					new SqlParameter("@AgentID", AgentID)
			};

            AgentUser model = new AgentUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public AgentUser GetModel(int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserID,AgentID,GroupID,LoginName,Password,CreateTime,Status,TrueName,MobilePhone,Phone,Address,LastLoginTime,LastLoginIP,IsOnline,IsLimit,Note from permission.[User] ");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            AgentUser model = new AgentUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// <param name="loginname">登录名</param>
        /// <param name="password">密码 注意该字段为解密后</param>
        /// </summary>
        public AgentUser GetModel(string loginname, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserID,AgentID,GroupID,LoginName,Password,CreateTime,Status,TrueName,MobilePhone,Phone,Address,LastLoginTime,LastLoginIP,IsOnline,IsLimit,Note from permission.[User] ");
          
            strSql.Append(" where LoginName=@LoginName and Password=@Password");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", loginname),
                    new SqlParameter("@Password",password)
			};

            AgentUser model = new AgentUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public AgentUser DataRowToModel(DataRow row)
        {
            AgentUser model = new AgentUser();
            if (row != null)
            {
                if (row["UserID"] != null && row["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(row["UserID"].ToString());
                }
                if (row["AgentID"] != null && row["AgentID"].ToString() != "")
                {
                    model.AgentID = int.Parse(row["AgentID"].ToString());
                }
                if (row["GroupID"] != null && row["GroupID"].ToString() != "")
                {
                    model.GroupID = int.Parse(row["GroupID"].ToString());
                }
                if (row["LoginName"] != null)
                {
                    model.LoginName = row["LoginName"].ToString();
                }
                if (row["Password"] != null)
                {
                    model.Password = row["Password"].ToString();
                }
                if (row["CreateTime"] != null)
                {
                    model.CreateTime = row["CreateTime"].ToString();
                }
                if (row["Status"] != null && row["Status"].ToString() != "")
                {
                    model.Status = int.Parse(row["Status"].ToString());
                }
                if (row["TrueName"] != null)
                {
                    model.TrueName = row["TrueName"].ToString();
                }
                if (row["MobilePhone"] != null)
                {
                    model.MobilePhone = row["MobilePhone"].ToString();
                }
                if (row["Phone"] != null)
                {
                    model.Phone = row["Phone"].ToString();
                }
                if (row["Address"] != null)
                {
                    model.Address = row["Address"].ToString();
                }
                if (row["LastLoginTime"] != null)
                {
                    model.LastLoginTime = row["LastLoginTime"].ToString();
                }
                if (row["LastLoginIP"] != null)
                {
                    model.LastLoginIP = row["LastLoginIP"].ToString();
                }
                if (row["IsOnline"] != null && row["IsOnline"].ToString() != "")
                {
                    model.IsOnline = int.Parse(row["IsOnline"].ToString());
                }
                if (row["IsLimit"] != null && row["IsLimit"].ToString() != "")
                {
                    model.IsLimit = int.Parse(row["IsLimit"].ToString());
                }
                if (row["Note"] != null)
                {
                    model.Note = row["Note"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 得到登录权限信息
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="ip"></param>
        /// <param name="_con"></param>
        /// <returns></returns>
        public List<LoginModuleEntity> GetUserLogin(int uid, string ip)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int),
                    new SqlParameter("@LastLoginIP",SqlDbType.VarChar,15)};
            parameters[0].Value = uid;
            parameters[1].Value = ip;
            List<LoginModuleEntity> list = new List<LoginModuleEntity>();
            DataTable dt = DbHelperSQL.RunProcedure("[Permission].[uspUserLogin]", parameters, "temp").Tables[0];
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    LoginModuleEntity model = new LoginModuleEntity(
                        dr["ModuleTypeName"].ToString(),
                        dr["ModuleName"].ToString(),
                        dr["ModuleURL"].ToString(),
                        Convert.ToInt32(dr["RoleID"].ToString()),
                        Convert.ToInt32(dr["ModuleID"].ToString()),
                        dr["AuthorityTag"].ToString(),
                        Convert.ToInt32(dr["ModuleOrder"].ToString()),
                        Convert.ToInt32(dr["ModuleTypeOrder"].ToString()),
                        Convert.ToInt32(dr["Display"].ToString()),
                        dr["Disabled"].ToInt32()
                        );
                    list.Add(model);
                }
            }
            return list;
        }
    }
}
